Rules Passing Fraction Roll-Up Report

What is a Rules Passing Fraction Roll-Up Report?

The Rules Passing Fraction Roll-Up report provides the passing and total rows scanned for user-defined rules over the past 30 days, which are aggregated by dimensions.

Steps

  1. Sign in to the Collibra DQ instance and click the reports icon Reports icon in the left navigation pane.
    The Reports page opens.

  2. From the Reports page, click the Rules Passing Fraction Roll-Up link.

  3. To configure the type of chart configuration and data you want to see in the chart, click the ellipsis icon.

  4. Click Chart Configuration.
    The Chart Configuration window displays.

    Note As a best practice, keep the Chart Configuration as the default.

  5. For x-axis, select one of the following options from the X-Axis Column dropdown list:
    • avg_percent_rows_passing
    • dimension

  6. For y-axis, select one or more of the following options from the Y-Axis Columns dropdown list:

    • dimension
    • avg_observability_score

  7. For the type of chart you want to display, select one of the following options from the Chart Type dropdown list:

    • Line
    • Area
    • Column
    • Scatter

  8. Click Save.

  9. Navigate the pages of your report by clicking the Previous and Next pagination buttons, located bottom-right of the columns.

Sample SQL query

You can use the following sample SQL query for a Rules Passing Fraction Roll-Up report.

Copy 
WITH a AS (
    SELECT * FROM rule_output
    WHERE updt_ts BETWEEN (NOW() - INTERVAL '30 DAY') AND NOW()
),
b AS (
    SELECT * FROM dataset_scan WHERE rc > 1
),
c AS (
    SELECT * FROM owl_rule
),
e AS (
    SELECT * FROM dq_dimension
),
g AS (
    SELECT * FROM owl_catalog
),
h AS (
    SELECT * FROM business_unit_to_dataset
),
i AS (
    SELECT * FROM business_units
),
j AS (
    SELECT DISTINCT dataset, col_nm, col_semantic FROM dataset_schema
),
f AS (
    SELECT
        a.dataset,
        a.rule_nm,
        CASE 
            WHEN c.column_name = '' OR c.column_name IS NULL THEN 'global'
            ELSE c.column_name 
        END AS column_name,
        COALESCE(e.dim_name, 'UNSPECIFIED') AS dim_name,
        c.dim_id,
        ROUND(a.perc) AS perc,
        ROUND(a.perc * b.rc) AS breaking_rows,
        100 - ROUND((a.perc * b.rc) / CAST(b.rc AS DECIMAL)) AS score,
        b.rc AS row_count,
        a.run_id,
        g.alias,
        g.catalog_rank,
        g.db_nm,
        g.run_mode,
        g.source_name,
        g.table_nm,
        i.name,
        j.col_nm,
        j.col_semantic
    FROM a
    LEFT JOIN b ON a.dataset = b.dataset AND a.run_id = b.run_id
    INNER JOIN c ON a.dataset = c.dataset AND a.rule_nm = c.rule_nm
    LEFT JOIN e ON e.dim_id = c.dim_id
    INNER JOIN g ON g.dataset = a.dataset
    LEFT JOIN h ON h.dataset = g.dataset
    LEFT JOIN i ON i.id = h.id
    LEFT JOIN j ON a.dataset = j.dataset AND c.column_name = j.col_nm
)
SELECT
    CASE 
        WHEN AVG(score) < 0 THEN 0
        ELSE AVG(score)
    END AS avg_percent_rows_passing,
    dim_name AS dimension
FROM f
GROUP BY dim_name;